package com.zhiyou.app.shop.website.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.stereotype.Component;

import com.zhiyou.app.shop.common.util.Page;
import com.zhiyou.app.shop.common.util.ShopConstants;
import com.zhiyou.app.shop.manager.dao.impl.BaseHibernateDAO;
import com.zhiyou.app.shop.website.dao.IUserStockInfoDao;
import com.zhiyou.app.shop.website.dao.pojo.UserStockInfo;

@Component
public class UserStockInfoDaoImpl extends BaseHibernateDAO<UserStockInfo, String> implements IUserStockInfoDao{

	public UserStockInfoDaoImpl(){
		super(UserStockInfo.class);
	}

	public BigDecimal findAllSaleByDay(final String userId, final String date) {
		final String sql = "select sum(us.stock_quantity) as total from user_stock_info us where us.user_id = ? and create_date = ? group by us.user_id";
		
		BigDecimal bd = hibernateTemplate.execute(new HibernateCallback<BigDecimal>() {

			public BigDecimal doInHibernate(Session session) throws HibernateException{
				SQLQuery query = session.createSQLQuery(sql);
				query.setString(0, userId);
				query.setString(1, date);
				BigDecimal total = (BigDecimal)query.addScalar("total", new org.hibernate.type.BigDecimalType()).uniqueResult(); 
				if(total == null){
					return BigDecimal.ZERO;
				}
				return total;
			}
		});
		return bd;
	}

	//卖出的
	public Integer listed(final String userId) {
		final String sql = "select count(1) as cnt from user_stock_info where user_id=? and type='1'";
		Integer bd = hibernateTemplate.execute(new HibernateCallback<Integer>() {

			public Integer doInHibernate(Session session) throws HibernateException{
				SQLQuery query = session.createSQLQuery(sql);
				query.setString(0, userId);
				return (Integer)query.addScalar("cnt", new org.hibernate.type.IntegerType()).uniqueResult();
			}
		});
		return bd;
	}
	
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> findStockInfo(String stockType,
			String userType, Page page) {
		final StringBuffer sql=new StringBuffer();
		final Map<String,Object> param=new HashMap<String,Object>();
		//查询SQL
		sql.append("SELECT" +
				"	st.id," +
				"	st.user_id," +
				"	u.user_account," +
				"	st.stock_quantity," +
				"	st.price," +
				"	st.total_price," +
				"	'挂牌' AS state," +
				"	st.type," +
				"	st.`status`," +
				"	st.trade_date," +
				"	st.trade_date end_date," +
				"	st.trade_date complete_date " +
				"FROM	user_stock_info st " +
				"left join `user` u  " +
				"on st.user_id=u.user_account " +
				"where 1=1 ");
		//按用户查看交易
		if(ShopConstants.MEMBER.equals(userType)||ShopConstants.MEMBER_COMPANY.equals(userType)){
			sql.append(" and u.role =:userRole ");
			param.put("userRole", userType);
		}
		//按交易类型查看
		if(ShopConstants.STOCK_INFO_BUY.equals(stockType)||ShopConstants.STOCK_INFO_SELL.equals(stockType)){
			sql.append(" and st.type=:stType ");
			param.put("stType", stockType);
		}
		sql.append(" limit :startNum , :endNum ");
		param.put("startNum", page.getOffset());
		param.put("endNum", page.getPageSize());
		List<Map<String, Object>> bd = hibernateTemplate.execute(new HibernateCallback<List<Map<String, Object>>>() {

			public List<Map<String, Object>> doInHibernate(Session session) throws HibernateException{
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setProperties(param);
				return query.list();
			}
		});
		return bd;
	}

	public int findStockInfoCount(String stockType, String userType) {
		final StringBuffer sql=new StringBuffer();
		final Map<String,Object> param=new HashMap<String,Object>();
		//查询SQL
		sql.append("SELECT count(1) as cnt  " +
				"FROM	user_stock_info st  " +
				"left join `user` u " +
				"on st.user_id=u.user_account " +
				"where 1=1  ");
		//按用户查看交易
		if(ShopConstants.MEMBER.equals(userType)||ShopConstants.MEMBER_COMPANY.equals(userType)){
			sql.append(" and u.role =:userRole ");
			param.put("userRole", userType);
		}
		//按交易类型查看
		if(ShopConstants.STOCK_INFO_BUY.equals(stockType)||ShopConstants.STOCK_INFO_SELL.equals(stockType)){
			sql.append(" and st.type=:stType ");
			param.put("stType", stockType);
		}
		Integer bd = hibernateTemplate.execute(new HibernateCallback<Integer>() {

			public Integer doInHibernate(Session session) throws HibernateException{
				SQLQuery query = session.createSQLQuery(sql.toString());
				query.setProperties(param);
				return (Integer)query.addScalar("cnt", new org.hibernate.type.IntegerType()).uniqueResult();
			}
		});
		return bd;
	}

}
